Amazon Redshift Useful SQL: VACUUM処理が必要なテーブルを洗い出す

Amazon Redshiftに於いて、『VACUUM』という処理は重要な要素となっています。適宜タイミングを見計らって実施する事でテーブルの情報を整理しソートされ、結果としてアクセスに対するレスポンス向上も見込めます。一方でVACUUM処理自体に必要となるリソースも多くなる事から、実施タイミングについても業務時間やピークタイムを避けて行う等の対処が求められたりします。VACUUMの処理については範囲としては『クラスタ全体』『テーブル単位』のいずれか、処理の種類としては『DELETE ONLY』『SORT ONLY』『DELETE/SORT両方』が選択可能です。詳細については以下AWSドキュメントを御参照ください。

• VACUUM - Amazon Redshift
• テーブルのバキューム処理 - Amazon Redshift

そこで1つの疑問がふと浮かびます。『んじゃそのVACUUM処理、いつ実施すれば良いの？』と。

そこで当エントリではその指針となる情報を得られるSQLについて見てみたいと思います。

情報を当たってみると、VACUUM処理に関する情報は以下STV_TBL_PERMテーブルにその内容が含まれている様です。こ

• STV_TBL_PERM - Amazon Redshift

の情報を元にSQLを作ってみます。それがこちら。

/** 0.VACUUM処理が行われていないテーブル群 */
select
  '0_not_sorted' as status,
  sum_result.tablename,
  sum_result.sorted_rows,
  sum_result.rows,
  cast(0 as decimal(5,3)) as sort_percentage
from
(select
  trim(name) as tablename,
  sum(sorted_rows) as sorted_rows,
  sum(rows) as rows
from
  stv_tbl_perm
group by name
order by name) sum_result
where
  sorted_rows = 0
UNION ALL
/** 1.VACUUM処理が行われているテーブル群 */
select
  '1_sorted' as status,
  sum_result.tablename,
  sum_result.sorted_rows,
  sum_result.rows,
  cast(
    cast(sum_result.sorted_rows as double precision) / cast(sum_result.rows as double precision)
    as decimal(5,3)
  ) as sort_percentage
from
(select
  trim(name) as tablename,
  sum(sorted_rows) as sorted_rows,
  sum(rows) as rows
from
  stv_tbl_perm
group by name
order by name) sum_result
where
  sorted_rows != 0
order by
  status asc,
  sort_percentage asc,
  rows desc;

実行すると結果がこのような形で表示されます。sort_percentageの部分がソート済状況を示す数値となっており、0.000は完全未ソート、1.000が完全ソート済の状態となります。この例を見ると先ず実行すべきは0_not_sortedのステータスとなっているテーブル、次いで100%に満たないテーブルを順次行っていく...となります。

    status    |             tablename             | sorted_rows |    rows    | sort_percentage 
--------------+-----------------------------------+-------------+------------+-----------------
 0_not_sorted | table_aaa                         |           0 | 1000000000 |           0.000
 0_not_sorted | table_bbb                         |           0 |  500000000 |           0.000
 0_not_sorted | table_ccc                         |           0 |   10000000 |           0.000
 :
 0_not_sorted | table_fff                         |           0 |        300 |           0.000
 1_sorted     | table_001                         |        1000 |      10000 |           0.100
 1_sorted     | table_002                         |        6000 |      30000 |           0.200
 1_sorted     | table_003                         |     7500000 |   10000000 |           0.750
 :
 1_sorted     | table_019                         |   987654321 |  987654321 |           1.000
 1_sorted     | table_020                         |  1234567890 | 1234567890 |           1.000

追記：

ソート状態が100%(VACUUMの必要無し)、及び現在の行数が0のものを省いた情報を抽出するSQL。上記SQLに条件を上被せしただけですけど、『対処すべきテーブルはどれか』を出すのに使えるかと思います。

SELECT * FROM 
(
  /** 0.VACUUM処理が行われていないテーブル群 */
  select
    '0_not_sorted' as status,
    sum_result.tablename,
    sum_result.sorted_rows,
    sum_result.rows,
    cast(0 as decimal(5,3)) as sort_percentage
  from
  (select
    trim(name) as tablename,
    sum(sorted_rows) as sorted_rows,
    sum(rows) as rows
  from
    stv_tbl_perm
  group by name
  order by name) sum_result
  where
    sorted_rows = 0
  UNION ALL
  /** 1.VACUUM処理が行われているテーブル群 */
  select
    '1_sorted' as status,
    sum_result.tablename,
    sum_result.sorted_rows,
    sum_result.rows,
    cast(
      cast(sum_result.sorted_rows as double precision) / cast(sum_result.rows as double precision)
      as decimal(5,3)
    ) as sort_percentage
  from
  (select
    trim(name) as tablename,
    sum(sorted_rows) as sorted_rows,
    sum(rows) as rows
  from
    stv_tbl_perm
  group by name
  order by name) sum_result
  where
    sorted_rows != 0
  order by
    status asc,
    sort_percentage asc,
    rows desc
) all_vacuum_percentage
WHERE
  (all_vacuum_percentage.sort_percentage != 1) AND
  (all_vacuum_percentage.rows != 0);

まとめ

以上、Amazon Redshiftに於けるVACUUM処理が必要なテーブルを洗い出すSQLのご紹介でした。クラスタ全体に対してフルVACUUM処理を行うと(データ量にも拠りますが)非常に長い時間を要する為、夜中等ピークを避けて実行したとしてももしかしたら業務時間に影響を与えてしまうかもしれません。常に100%ソート済である状態がベストなのだとは思いますがそのような理由から常に実施が行えない場合もあるかとも思います。その際テーブル毎に処理を行うという方針を取る場合にこの辺りの情報を参考にVACUUM処理対象のテーブルを選定して行くと良いのではないでしょうか。こちらからは以上です。